package com.chenxi.vhr.utils;


//import com.chenxi.vhr.bean.BaseUser;
import com.chenxi.vhr.bean.common.NasExcelField;
import com.chenxi.vhr.bean.common.NasExcelForm;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author : zhangchenxi
 * @create : 2022-02-11
 **/
public class ExcelUtil<T> {

    public Class<T> clazz;

    private List<NasExcelField> fieldList;

    private Integer rowNum = 0;

    private Integer columnNum = 0;

    private Integer lastColumnNum = 0;

    private HSSFWorkbook workbook;

    private HSSFSheet sheet;

    public ExcelUtil(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    /**
     * 将file文件中的数据解析到List中
     * @param file
     * @param fieldList
     */
    public List<T>  parseExcelToList(MultipartFile file, List<String> fieldList) throws IOException, IllegalAccessException, ParseException, InstantiationException {
        List<T> list = new ArrayList<>();
        // 创建workbook对象
        HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
        // 获取sheet数量
        int sheetNums = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetNums; i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);
            // 获取行
            int rowNums = sheet.getPhysicalNumberOfRows();
            T vo = null;
            // 前两行跳过：主标题 + 标题
            for (int j = 2; j < rowNums; j++) {
                // 获取行
                HSSFRow row = sheet.getRow(j);
                vo = clazz.newInstance();
                if (row == null) {
                    // 防止有空行
                    continue;
                }
                int columns = row.getPhysicalNumberOfCells();
                Field[] declaredFields = vo.getClass().getDeclaredFields();
                Field[] superDeclaredFields = vo.getClass().getSuperclass().getDeclaredFields();
                for (int k = 0; k < fieldList.size(); k++) {
                    String property = fieldList.get(k);
                    if (StringUtil.isNull(property)) {
                        continue;
                    }
                    HSSFCell cell = row.getCell(k);
                    setValueToObj(vo, cell, property, declaredFields);
                    setValueToObj(vo, cell, property, superDeclaredFields);
                }
                list.add(vo);
            }
        }
        return list;
    }

    /**
     *  为 【对象】 设置值
     * @param obj
     * @param cell
     * @param property
     * @param fields
     * @throws IllegalAccessException
     * @throws ParseException
     */
    private void setValueToObj(T obj, HSSFCell cell, String property, Field[] fields) throws IllegalAccessException, ParseException {
        for (Field field : fields){
            if (!field.getName().equals(property)) {
                continue;
            }
            field.setAccessible(true);
            Type genericType = field.getGenericType();
            if (genericType.getTypeName().equals(String.class.getName())) {
                if(cell.getCellType().equals(CellType.NUMERIC)) {
                    double numericCellValue = cell.getNumericCellValue();
                    Integer integer = StringUtil.asInt(numericCellValue);
                    field.set(obj, StringUtil.asString(integer));
                } else {
                    field.set(obj, cell.getStringCellValue());
                }
            } else if (genericType.getTypeName().equals(Integer.class.getName())) {
                field.set(obj, cell.getNumericCellValue());
            } else if (genericType.getTypeName().equals(Double.class.getName())) {
                field.set(obj, cell.getNumericCellValue());
            } else if (genericType.getTypeName().equals(Long.class.getName())) {
                field.set(obj, cell.getNumericCellValue());
            } else if (genericType.getTypeName().equals(Date.class.getName())) {
                String stringCellValue = cell.getStringCellValue();
                field.set(obj, DateUtil.asDate(stringCellValue));
            } else {
                field.set(obj, cell.getStringCellValue());
            }
        }
    }

    /**
     *  对list数据源将其里面的数据导入到excel表单
     * @param response 返回数据
     * @param form  前端传递的
     * @param list 导出数据集合
     * @return 结果
     * @throws IOException
     */
    public void exportExcel(HttpServletResponse response, NasExcelForm form, List<T> list) throws Exception {
        // 1、初始化form
        initForm(form);
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 创建Excel文档
        workbook = new HSSFWorkbook();
        // 2、文档摘要信息设置
        initDocumentInfo(form.getFileTitle());
        // 创建sheet
        sheet = workbook.createSheet();
        if(!StringUtil.isNull(form.getSheetName())) workbook.setSheetName(0, form.getSheetName());
        // 3、列信息设置
        initColumnInfo();
        // 4、设置主标题
        initMainTitle(form.getFileTitle());
        // 5、创建标题行
        initTitleByFieldList();
        // 6、数据填充
        loadData(list);
        workbook.write(response.getOutputStream());//写到输出流
    }

    /**
     * 1、初始化form
     * 初始化前台传递的参数
     */
    private void initForm(NasExcelForm form) {
        this.fieldList = form.getFieldList();
        this.lastColumnNum = fieldList.size() - 1;
        if (StringUtil.isNull(form.getFileTitle())) {
            form.setFileTitle("");
        }
    }

    /**
     * 2、文档摘要信息设置
     * @param title
     */
    private void initDocumentInfo(String title) {
        // BaseUser currentUser = SystemUtil.getCurrentUser();
        workbook.createInformationProperties();
        DocumentSummaryInformation information = workbook.getDocumentSummaryInformation();
        // 文档类别
        information.setCategory("Excel");
        // 设置管理员
        information.setManager("晨曦");
        // 设置公司信息
        information.setCompany("晨曦科技");
        // 获取文档摘要信息
        SummaryInformation summaryInformation = workbook.getSummaryInformation();
        // 标题
        summaryInformation.setTitle(title);
        // 作者
        // summaryInformation.setAuthor(currentUser.getNickName());
        // 备注
        // summaryInformation.setComments("文档由 " + currentUser.getNickName()  + " 提供");
    }

    /**
     * 3、列信息设置
     */
    private void initColumnInfo() {
        fieldList.forEach(field -> {
            int columnWidth = StringUtil.asInt(MathUtil.multiply(MathUtil.divide(field.getWidth(), 10, 0), 256));
            sheet.setColumnWidth(field.getIndex(),  columnWidth);
        });
    }

    /**
     * 4、设置主标题
     * @param title
     */
    private void initMainTitle(String title) {
        if (StringUtil.isNull(title)) {
            return ;
        }
        HSSFRow row = sheet.createRow(rowNum);
        // 行高40 px = 30pt = 600 twips
        row.setHeight((short)(30 * 20));
        // 合并行
        CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum, rowNum, 0,  this.lastColumnNum);
        sheet.addMergedRegion(cellRangeAddress);
        // 设置字号和字体大小
        HSSFCell cell = row.createCell(0);
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);
        cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(title);
        rowNum++;
    }

    /**
     * 5、创建标题行
     */
    private void initTitleByFieldList() {
        // 创建行
        HSSFRow row = sheet.createRow(rowNum);
        fieldList.forEach(field -> {
            // 创建样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            if (NasExcelField.align_center.equals(field.getAlign())) {
                headerStyle.setAlignment(HorizontalAlignment.LEFT);
            } else if (NasExcelField.align_left.equals(field.getAlign())) {
                headerStyle.setAlignment(HorizontalAlignment.CENTER);
            } else if (NasExcelField.align_right.equals(field.getAlign())) {
                headerStyle.setAlignment(HorizontalAlignment.RIGHT);
            }
            Cell cell = row.createCell(field.getIndex());
            cell.setCellStyle(headerStyle);
            cell.setCellValue(field.getLabel());
        });
        rowNum++;
    }

    /**
     * 6、数据填充
     * @param list
     */
    private void loadData(List<T> list) throws Exception {
        try {
            columnNum = fieldList.size();
            int count = 1;
            for(T item: list) {
                Row row = sheet.createRow(rowNum);
                for (int i = 0; i < fieldList.size(); i++) {
                    NasExcelField columnField = fieldList.get(i);
                    Cell cell = row.createCell(columnField.getIndex());
                    if (columnField.getIndex() == 0) {
                        // 编号
                        cell.setCellValue(count++);
                    } else {
                        String property = columnField.getProperty();
                        Field[] declaredFields = clazz.getDeclaredFields();
                        setValueToCell(cell, declaredFields, item, property);
                        Field[] superDeclaredFields = clazz.getSuperclass().getDeclaredFields();
                        setValueToCell(cell, superDeclaredFields, item, property);
                    }
                }
                rowNum++;
            }
        } catch (IllegalAccessException e) {
            throw new Exception("导出异常：导出列不存在！");
        }
    }

    /**
     *  为Cell【单元格】设置值
     */
    private void setValueToCell(Cell cell, Field[] fields, T item, String property) throws IllegalAccessException {
        for(Field field : fields) {
            if (!field.getName().equals(property)) {
                continue;
            }
            field.setAccessible(true);
            Type genericType = field.getGenericType();
            if (genericType.getTypeName().equals(String.class.getName())) {
                cell.setCellValue(field.get(item).toString());
            } else if (genericType.getTypeName().equals(Integer.class.getName())) {
                cell.setCellValue(Integer.parseInt(field.get(item).toString()));
            } else if (genericType.getTypeName().equals(Double.class.getName())) {
                cell.setCellValue(Double.parseDouble(field.get(item).toString()));
            } else if (genericType.getTypeName().equals(Long.class.getName())) {
                cell.setCellValue(Long.parseLong(field.get(item).toString()));
            } else if (genericType.getTypeName().equals(Date.class.getName())) {
                cell.setCellValue(DateUtil.asString((Date) field.get(item)));
            } else {
                cell.setCellValue(field.get(item).toString());
            }
        }
    }
}
